Microsoft Access: Отчеты

Цель: приобретение умений проектирования реляционной базы данных на примере Microsoft Access

Программное обеспечение: Microsoft Access 2007 либо новее

На последующих занятиях мы будем разрабатывать новую базу данных. Созданная ранее база данных «Расписание» - очень примитивная (можно сказать, разминочная). Теперь займемся более серьезной задачей. Серьезной настолько, что ее решение успешно применяется в работе заведующего кафедрой.

Постановка задачи

Требуется создать информационную систему, упрощающую заведующему кафедрой распределение академической нагрузки между преподавателями. Условия

На кафедре работает некоторое количество сотрудников (для примера 10). Каждый сотрудник занимает одну или даже несколько должностей. В зависимости от должности, преподавателю положена определенная норма часов академической нагрузки. Разрешается немного отступать от нормы в случае особой необходимости.

Если сотрудник занимает две должности (например, старший преподаватель на одну ставку и преподаватель на четверть ставки), то по каждой из должностей требуется выделить свою норму академических часов.

Перед началом учебного года деканаты представляют на кафедру сведения о том, какие дисциплины и в каком объеме (количество часов лекций, практических. лабораторных и т.д.) необходимо распределить между преподавателями.

Учебная нагрузка может быть разделена на небольшие неделимые порции. Одну такую порцию можно отдать не более чем одному преподавателю (разделить одну порцию на двоих уже невозможно). Пример такой порции: лабораторные занятия в одной подгруппе по предмету ИСИС на четвертом курсе в шестом семестре специальности МИ. Очевидно, что один преподаватель должен получить много таких порций, чтобы уложиться в норму нагрузки.

Полученная кафедрой нагрузка должна быть распределена так, чтобы:

1) ни одна порция не осталась нераспределенной (каждой порции нагрузки должен быть сопоставлен выполняющий ее сотрудник);

2) ни одна порция не может достаться сазу нескольким преподавателям;

3) каждый сотрудник должен получить свою норму часов (отклонение от нормы не должно превышать 20 часов);

4) нагрузка распределяется на два полугодия, по возможности равномерно (перекос нагрузки более 60% процентов одном семестре приводит к переутомлению преподавателей).

Готовая база данных должна с помощью запросов и отчетов выдавать:

1) информацию о нагрузке для каждого преподавателя (для внесения в индивидуальный план работы);

2) о наличии отклонений от нормы и перекосов между семестрами;

3) о наличии нераспределенной нагрузки;

4) о сводном распределении нагрузки по факультетам и полугодиям для предоставления в деканаты.

Проектирование

Необходимо спроектировать структуру базы данных. Сначала рассмотрим совсем неправильный вариант, когда база данных состоит из всего одной таблицы. Каждая строчка таблицы будет содержать одну порцию нагрузки с указанием, что это за дисциплина, на какой специальности, на каком курсе и семестре преподается, сколько групп/подгрупп, студентов присутствует на занятиях, а также сведения о том, кто из преподавателей будет отвечать за выполнение это порции.

Таблица оказалась очень «широкой». Для ее просмотра можно увеличить масштаб этого документа либо открыть файл «Нагрузка_по_преподавателям.xlsx». Для удобства просмотра приведем две строки таблицы в «транспонированном» (повернутом на 90°) виде:

Мы не будем переносить эту ненормализованную таблицу в Access. Вместо этого сразу создадим структуру таблиц с учетом требований трех нормальных форм. На первых порах проигнорируем сведения о составе групп (количестве групп, подгрупп, студентов в группах), вернемся в следующих лабораторных занятиях.

Вам необходимо создать структуру таблиц следующего вида:

Обратите внимание, что многие таблицы имеют одинаковый набор полей: Код, Наименование, Наименование_кратк. Достаточно создать одну из этих таблицу, затем создать их копии с помощью Ctrl+C, Ctrl+V (указав опцию вставки «только данные»).

Структура таких таблиц имеет вид:

Приведем для определенности сведения об остальных таблицах (для Вас важнее всего сведения о типах данных). Таблица «Специальности» имеет очень похожую на другие справочные таблицы структуру, но с добавлением поля «Код_факультета». Тем самым можно указать, к какому факультету относится та или иная специальность: Таблица

Таблица «Персоналии» содержит персональные сведения о людях (о том, что данные персоны могут являться еще и работниками, речь пойдет дальше). Мы ограничимся только именами, оставив за кулисами другие личные данные (ученая степень/звание, дата рождения, телефон и т.п.):

Таблица «Работники» содержит сведения о том, на каких должностях работают учтенные выше персоны. Дело в том, что одна и та же персона может одновременно работать на двух должностях (например, Матысик Олег Викторович, персона с кодом 1, может занимать должность заведующего кафедрой на 1 ставку и еще доцента на 0,5 ставки). Структура таблицы такова:

Каждая запись в таблице «Нагрузка» соответствует порции академической нагрузки, выданной тому или иному работнику.

Заметим, что в этой таблице ВНЕЗАПНО отсутствуют сведения о том, какой специальности соответствует нагрузка:

Например, возможна примерно такая запись:

Разумеется, вместо текстовых записей размещаются коды-ссылки на другие таблицы. Так какой же специальности читаются эти лекции?

Дело в том, что лекции по предмету «Информационные системы и сети» читаются потоком на двух специальностях сразу.

Требования первой нормальной формы запрещают создание поля «Специальности» с записями вида «МИ, ФИ». Поэтому сведения о том, для каких специальностей предназначена та или иная порция нагрузки, хранится в отдельной таблице со странным названием «Нагрузка_Специальности»:

Записи этой таблицы не представляют собой самостоятельных сущностей, а лишь выражают связь вида «Многие-ко-многим» между таблицами «Нагрузка» и «Специальности».

Задания

1. Создайте новую базу данных «Нагрузка-ЭК4-ФамилияИО» или «Нагрузка-ЭК4-ФамилияИО» (указав свою фамилию и инициалы).

2. Воссоздайте структуру таблиц из введения к лабораторной работе.

3. Установите связи между таблицами.

4. Создайте файл-отчет «Лаб16-ЭК4-ФамилияИО.docx» или «Лаб5-"ЭК4-ФамилияИО.docx» с ответом на следующий вопрос: какие требования нормальных форм нарушены в первоначальной ненормализованной базе данных (конкретно – какие поля должны быть вынесены в другие таблицы базы данных и почему), как указанные проблемы разрешены в только что созданном Вами варианте базы данных.

В качестве ответа на задание прикрепите созданную Вами базу данных и файл-отчет с ответом на вопрос.